Imports Microsoft.SqlServer.Dts.Design
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Design
Imports System.Windows.Forms
Imports System.Data.OleDb

Public Class Excel2ConnMgrUIFormVB

#Region " Variables & constants "

    Private Const CONNECTIONNAME_BASE As String = "Excel2ConnectionManagerVB"

    Private _connectionManager As ConnectionManager
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1823:AvoidUnusedPrivateFields")> _
    Private _serviceProvider As IServiceProvider
    Private _connectionName As String
    Private _excelFile As String

#End Region

#Region " Properties "

    Public ReadOnly Property ConnectionName() As String
        Get
            Return _connectionName
        End Get
    End Property

#End Region

#Region " Form & control event handlers "

    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        With ofdBrowse
            .Title = "Select an existing Excel file"
            .Filter = "Excel files(*.xls)|*.xls"
            .InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Personal).ToString
        End With
        If ofdBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
            _excelFile = ofdBrowse.FileName
            lblExcelFile.Text = _excelFile
            _connectionName = CONNECTIONNAME_BASE & "." & System.IO.Path.GetFileName(_excelFile)
        End If
        EnableControls()
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        ConfigureConnectionManagerFromUI()
        Me.DialogResult = Windows.Forms.DialogResult.OK
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = Windows.Forms.DialogResult.Cancel
    End Sub

#End Region

#Region " Helper functions "

    Public Sub Initialize(ByVal connectionManager As ConnectionManager, ByVal serviceProvider As IServiceProvider)
        Me._connectionManager = connectionManager
        Me._serviceProvider = serviceProvider
        ConfigureUIFromConnectionManager()
        EnableControls()
    End Sub

    Private Sub EnableControls()
        Dim buttonState As Boolean
        buttonState = Not String.IsNullOrEmpty(lblExcelFile.Text)
        btnOK.Enabled = buttonState
    End Sub

    Private Sub ConfigureUIFromConnectionManager()
        Dim tempName As String
        Dim tempExcelFile As String
        With Me._connectionManager
            tempName = .Properties("Name").GetValue(Me._connectionManager).ToString
            If Not String.IsNullOrEmpty(tempName) Then
                Me._connectionName = tempName
            Else
                Me._connectionName = CONNECTIONNAME_BASE
            End If

            tempExcelFile = .Properties("ExcelFile").GetValue(Me._connectionManager).ToString
            If Not String.IsNullOrEmpty(tempExcelFile) Then
                Me._excelFile = tempExcelFile
                lblExcelFile.Text = _excelFile
            End If

            chkHDR.Checked = CType(.Properties("FirstRowHasColumnNames").GetValue(Me._connectionManager), Boolean)
            chkIMEX.Checked = CType(.Properties("UseImportMode").GetValue(Me._connectionManager), Boolean)
        End With
    End Sub

    Private Sub ConfigureConnectionManagerFromUI()
        With Me._connectionManager
            .Properties("Name").SetValue(Me._connectionManager, Me._connectionName)
            .Properties("ExcelFile").SetValue(Me._connectionManager, Me._excelFile)
            .Properties("FirstRowHasColumnNames").SetValue(Me._connectionManager, chkHDR.Checked)
            .Properties("UseImportMode").SetValue(Me._connectionManager, chkIMEX.Checked)
        End With
    End Sub

#End Region

End Class